using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using Firestarter.DB.Schema.BO;
using Firestarter.DB.Schema.Test.Logic;
using FireStarterModeller.BO;
using Firestarter.DB.Schema.Logic;
using FireStarterModeller.Test;
using Habanero.BO;
using Habanero.DB;
using Habanero.Testability;
using NUnit.Framework;
using Rhino.Mocks;

namespace Firestarter.DB.Schema.Test.Logic
{

    [TestFixture]
    public class TestDBReverseEng_GetSchema 
    {
        //TODO Brett 18 Jan 2009: Create view and make sure the views are not coming in as tables.
        //TODO Brett 18 Jan 2009: Create for all other databases
        protected IDbConnection _connection;
        protected TestUtilsDB _testUtilsDb;
        private int _testnum = 0;
        private string _baseTestDatabaseName;

        [TestFixtureSetUp]
        public virtual void SetupFixture()
        {
            TestUtilsDBSchema.LoadClassDefs();

            BORegistry.DataAccessor = new DataAccessorInMemory();
            _testUtilsDb = CreateSchemaTestDbUtil();
            _baseTestDatabaseName = _testUtilsDb.TestDatabaseName;
        }

        [SetUp]
        public virtual void SetupTest()
        {
            _testnum++;
            _testUtilsDb.TestDatabaseName = _baseTestDatabaseName + _testnum;
            Console.WriteLine("Creating Test database");
            _testUtilsDb.CreateTestDatabase();
            _connection = _testUtilsDb.CreateTestDBConnection();
        }

        [TearDown]
        public virtual void TearDown()
        {
            
            if (_connection != null)
            {
                _connection.Close();
                _connection.Dispose();
                _connection = null;
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }

            if (_testUtilsDb != null) { _testUtilsDb.DropDatabase(); }
        }
        private static string GetRandomString()
        {
            return RandomValueGen.GetRandomString();
        }
        protected virtual TestUtilsDB CreateSchemaTestDbUtil()
        {
            return new TestUtilsDB_MySql();
        }

        private static DBTableBuilder CreateNewDBTableBuilder()
        {
            return CreateNewDBTableBuilder(GetRandomString());
        }

        private static DBTableBuilder CreateNewDBTableBuilder(string tableName)
        {
            return new DBTableBuilder(new DBTable(tableName));
        }
    
        #region UtilityMethods

        protected static DBTable CreateTableInfoWithIDAndCol
            (out string idcolumnName, out string idcolumnType, out string columnName, out string columnDataType)
        {
            DBTable dbTable = CreateTableInfoWithID(out idcolumnName, out idcolumnType);
            columnName = AddColumn(dbTable, out columnDataType);
            return dbTable;
        }

        private static string AddColumn(DBTable dbTable, out string columnDataType)
        {
            string columnName = "MyColumn" + GetRandomInt();
            columnDataType = "VARCHAR";
            dbTable.Columns.Add(new DBColumn(columnName) {DataType = columnDataType, DataLength = 50});
            return columnName;
        }

        protected static DBTable CreateTableInfoWithID(out string idcolumnName, out string idcolumnType)
        {
            int idColumnLength;
            return CreateTableInfoWithID(out idcolumnName, out idcolumnType, out idColumnLength);
        }

        protected static DBTable CreateTableInfoWithID(out string idcolumnName, out string idcolumnType, out int idColumnLength)
        {
            DBTable dbTable = new DBTable("table" + GetRandomInt()) { PrimaryKeyDB = new DBPrimaryKey() };
            idcolumnName = "IDColumn";
            idcolumnType = "CHAR";
            idColumnLength = 38;
            DBColumn pkDBColumn = new DBColumn(idcolumnName)
            {
                DataType = idcolumnType, 
                Nullable = false,
                DataLength = idColumnLength
            };
            dbTable.Columns.Add(pkDBColumn);
            dbTable.PrimaryKeyDB.AddColumnInfo(pkDBColumn);
            //            dbTable.PrimaryKeyDB.PrimaryKeyColumns.Add(new DBPrimaryKeyColumn {Column = pkDBColumn});
            return dbTable;
        }

        protected static DBTable CreateTableInfoWithAutoIncID(out string idcolumnName, out string idcolumnType)
        {
            DBTable dbTable = new DBTable("table" + GetRandomInt()) { PrimaryKeyDB = new DBPrimaryKey() };
            idcolumnName = "IDColumn";
            idcolumnType = "int";
            DBColumn pkDBColumn = new DBColumn(idcolumnName)
                                      {
                                          DataType = idcolumnType,
                                          Nullable = false,
                                          IsAutoIncrementing = true
                                      };
            dbTable.Columns.Add(pkDBColumn);
            dbTable.PrimaryKeyDB.AddColumnInfo(pkDBColumn);
            //            dbTable.PrimaryKeyDB.PrimaryKeyColumns.Add(new DBPrimaryKeyColumn {Column = pkDBColumn});
            return dbTable;
        }


//        private void CreateTableWithForeignKey_CreateReferencedTable()
//        {
//            IDbConnection connection = CreateTestDBConnection();
//            Table referencedTableInfo;
//            Table tableInfo = CreateTableInfoWithForeignKey(out referencedTableInfo);
//            DropTable(tableInfo, connection);
//            CreateTableInDatabase(referencedTableInfo, connection);
//            CreateTableInDatabase(tableInfo, connection);
//        }
        protected static void ExecuteSql(IDbConnection connection, string sql)
        {
            if (connection.State == ConnectionState.Closed) connection.Open();
            IDbCommand command = connection.CreateCommand();
            command.CommandText = sql;
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
//                ex.Message += " "  + command.CommandText;
                throw new DatabaseWriteException(ex.Message + Environment.NewLine + command.CommandText, ex);
            }
        }

        private string CreateTable(IDbConnection idbConnection)
        {
            DBTable dbTableWithIDAndCol = GetTableInfoWithIDAndCol();
            CreateTableInDatabase(dbTableWithIDAndCol, idbConnection);
            return dbTableWithIDAndCol.TableName;
        }

        private static DBTable CreateTableInfoWithCompositeID()
        {
            string idcolumnName, idcolumnType, columnName, columnDataType;
            DBTable dbTable = CreateTableInfoWithIDAndCol
                (out idcolumnName, out idcolumnType, out columnName, out columnDataType);
            DBColumn dbColumn = dbTable.GetColumn(columnName);
            dbColumn.Nullable = false;
            dbTable.PrimaryKeyDB.AddColumnInfo(dbColumn);
            return dbTable;
        }

        protected void CreateTableInDatabase(DBTable dbTable, IDbConnection connection)
        {
            DatabaseScriptCreator databaseScriptCreator = GetDatabaseScriptCreator();
            string sqlRelatedTable = databaseScriptCreator.CreateTable(dbTable, GetSqlBuilderCreateTable(dbTable.TableName));

            try
            {
                DropTable(dbTable, connection);
            }
            catch (DatabaseWriteException ex)
            {
                Console.WriteLine(ex);
                return;
            }
            ExecuteSql(connection, sqlRelatedTable);
        }

        protected virtual CreateTableSqlBuilder GetSqlBuilderCreateTable(string tableName)
        {
            return new CreateTableSqlBuilder_MySql(tableName);
        }

        protected virtual DatabaseScriptCreator GetDatabaseScriptCreator()
        {
            return new DatabaseScriptCreator();
        }

        protected static DBTable GetTableInfoWithIDAndCol()
        {
            string idcolumnName, idcolumnType, columnName, columnDataType;
            return CreateTableInfoWithIDAndCol(out idcolumnName, out idcolumnType, out columnName, out columnDataType);
        }

        protected static DBTable GetTableInfoWithIDAndAltKey()
        {
            string idcolumnName, idcolumnType, columnName, columnDataType;
            DBTable dbTable = CreateTableInfoWithIDAndCol
                (out idcolumnName, out idcolumnType, out columnName, out columnDataType);
            AddAlternateKey(dbTable, columnName);
            return dbTable;
        }

        protected static DBTable GetTableInfoWithIDAndTwoAltKeys()
        {
            DBTable dbTable = GetTableInfoWithIDAndAltKey();
            string column2Name = AddColumn(dbTable);
            AddAlternateKey(dbTable, column2Name);
            return dbTable;
        }

        private static DBTable GetTableInfoWithIDAndCompositeAltKeys()
        {
            DBTable dbTable = GetTableInfoWithIDAndAltKey();
            string column2Name = AddColumn(dbTable);
            AddColumnToCompositeKey(dbTable.Keys[0], dbTable.GetColumn(column2Name));
            return dbTable;
        }

        private static DBTable CreateTableInfoWithForeignKey(out DBTable referencedDBTable)
        {

            DBTableBuilder referencedDbTableBuilder = CreateNewDBTableBuilder();
            referencedDbTableBuilder.AddPrimaryKeyColumn();
            referencedDBTable = referencedDbTableBuilder.Table;

            DBTableBuilder dbTableBuilder = CreateNewDBTableBuilder();
            dbTableBuilder.AddPrimaryKeyColumn();
            dbTableBuilder.AddForeignKey("MyForeignKey", referencedDBTable);
            return dbTableBuilder.Table;

            //Table parentDBTable = GetTableInfoWithIDAndAltKey();
            //referencedDBTable = AddForeignKeyToTable(parentDBTable);
            //return parentDBTable;
        }

        private static DBTable CreateTableInfoWithCompositeForeignKey(out DBTable referencedDBTable)
        {
            DBTableBuilder referencedDbTableBuilder = CreateNewDBTableBuilder();
            referencedDbTableBuilder.AddPrimaryKeyColumn();
            referencedDbTableBuilder.AddPrimaryKeyColumn("SecondIdCol","VARCHAR",50);
            referencedDBTable = referencedDbTableBuilder.Table;

            DBTableBuilder dbTableBuilder = CreateNewDBTableBuilder();
            dbTableBuilder.AddPrimaryKeyColumn();
            dbTableBuilder.AddForeignKey("MyCompForeignKey", referencedDBTable);
            return dbTableBuilder.Table;


            //Table parentDBTable = GetTableInfoWithIDAndAltKey();
            //referencedDBTable = AddCompositeForeignKeyToTable(parentDBTable);
            //return parentDBTable;
        }

        private static DBTable AddCompositeForeignKeyToTable(DBTable parentDBTable)
        {

            DBTableBuilder referencedDbTableBuilder = CreateNewDBTableBuilder();
            referencedDbTableBuilder.AddPrimaryKeyColumn();
            referencedDbTableBuilder.AddPrimaryKeyColumn("SecondIdCol", "VARCHAR", 50);
            DBTable referencedDBTable = referencedDbTableBuilder.Table;

            DBTableBuilder dbTableBuilder = new DBTableBuilder(parentDBTable);
            dbTableBuilder.AddForeignKey("MyCompForeignKey2", referencedDBTable);

            return referencedDBTable;


            //Table referencedDBTable = CreateTableInfoWithCompositeID();

            //DBForeignKey dbForeignKey = CreateForeignKey(parentDBTable, referencedDBTable);

            //AddColumnToForeignKey(parentDBTable, referencedDBTable, dbForeignKey, 0);
            //AddColumnToForeignKey(parentDBTable, referencedDBTable, dbForeignKey, 1);
            //FixForeignKeyDataTypes(dbForeignKey);
            //return referencedDBTable;
        }

        private static void FixForeignKeyDataTypes(DBForeignKey dbForeignKey)
        {
            foreach (DBForeignKeyColumn foreignKeyColumnInfo in dbForeignKey.ForeignKeyColumns)
            {
                foreignKeyColumnInfo.RelatedColumn.DataType = foreignKeyColumnInfo.ParentColumn.DataType;
            }
        }

        private static void AddColumnToForeignKey
            (DBTable parentDBTable, DBTable referencedDBTable, DBForeignKey dbForeignKey, int keyIndex)
        {
            string fkColumName = AddColumn(parentDBTable);
            DBColumn referencedPkDBColumn =
                referencedDBTable.PrimaryKeyDB.PrimaryKeyColumns[keyIndex].Column;
            AddForeignKeyColumn(parentDBTable, dbForeignKey, fkColumName, referencedPkDBColumn);
        }

        private static DBTable AddForeignKeyToTable(DBTable parentDBTable)
        {
            string referencedColumnName, referencedColumnType;
            DBTable referencedDBTable = CreateTableInfoWithID(out referencedColumnName, out referencedColumnType);

            DBForeignKey dbForeignKey = CreateForeignKey(parentDBTable, referencedDBTable);
            string fkColumName = AddColumn(parentDBTable);
            DBColumn referencedPkDBColumn = referencedDBTable.PrimaryKeyDB.PrimaryKeyColumns[0].Column;
            
            AddForeignKeyColumn(parentDBTable, dbForeignKey, fkColumName, referencedPkDBColumn);
            FixForeignKeyDataTypes(dbForeignKey);
            return referencedDBTable;
        }

        private static void AddForeignKeyColumn
            (DBTable parentDBTable, DBForeignKey dbForeignKey, string fkColumName, DBColumn referencedPkDBColumn)
        {
            dbForeignKey.ForeignKeyColumns.Add
                (new DBForeignKeyColumn
                     {
                         ParentColumn = parentDBTable.GetColumn(fkColumName),
                         RelatedColumn = referencedPkDBColumn
                     });
        }

        private static DBForeignKey CreateForeignKey(DBTable parentDBTable, DBTable referencedDBTable)
        {
            string foreignKeyName = "ForeignKeyName" + GetRandomInt();
            return new DBForeignKey
                       {
                           ForeignKeyName = foreignKeyName,
                           ParentTable = parentDBTable,
                           RelatedTable = referencedDBTable
                       };
        }


        protected static string AddColumn(DBTable info)
        {
            string dataType;
            return AddColumn(info, out dataType);
        }

        protected static void AddAlternateKey(DBTable dbTable, string columnName)
        {
            DBKey dbKey = new DBKey {KeyName = "Key" + GetRandomInt()};
            AddColumnToCompositeKey(dbTable, dbKey, columnName);
            dbTable.Keys.Add(dbKey);
        }

        private static int GetRandomInt()
        {
            return RandomValueGen.GetRandomInt();
        }

        private static void AddColumnToCompositeKey(DBTable dbTable, DBKey dbKey, string columnName)
        {
            DBKeyColumn dbKeyColumn = new DBKeyColumn {Column = dbTable.GetColumn(columnName)};
            dbKey.KeyColumns.Add(dbKeyColumn);
        }

        private static void AddColumnToCompositeKey(DBKey dbKey, DBColumn dbColumn)
        {
            dbKey.AddColumnInfo(dbColumn);
        }

        protected virtual void DropTable(DBTable referencedDBTable, IDbConnection connection)
        {
            ExecuteSql(connection, string.Format("DROP TABLE IF EXISTS `{0}`", referencedDBTable.TableName));
        }
        
        protected virtual string[] CreateParameters(string catalogName, string schemaName, string tableName, string identifierName)
        {
            if (identifierName != null) return new[] { catalogName, schemaName, tableName, identifierName };
            if (tableName != null) return new[] { catalogName, schemaName, tableName };
            if (schemaName != null) return new[] { catalogName, schemaName };
            if (catalogName != null) return new[] { catalogName };
            return null;
        }
        #endregion

        #region GetTable Tests


        [Test]
        public void Test_GetTablesDataTable_NoTable()
        {
            //---------------Set up test pack-------------------
            DBSchemaExtractor dbSchema = GetDbSchemaExtractor(_connection);
            //---------------Assert Precondition----------------
            //---------------Execute Test ----------------------
            DataTable tableDataTable = dbSchema.GetTablesDataTable();
            //---------------Test Result -----------------------
            Assert.IsNotNull(tableDataTable);
            Assert.AreEqual(0, tableDataTable.Rows.Count);
        }

        protected virtual DBSchemaExtractor GetDbSchemaExtractor(IDbConnection idbConnection)
        {
            return new DBSchemaExtractorMySQL(idbConnection);
        }

        [Test]
        public virtual void Test_GetSchema_OneTable()
        {
            //---------------Set up test pack-------------------
            DBTable dbTableWithIDAndCol = GetTableInfoWithIDAndCol();
            CreateTableInDatabase(dbTableWithIDAndCol, _connection);
            DbConnection dbConnection = (DbConnection)_connection;
            //            dbConnection.Open();
            //---------------Assert Precondition----------------
            //---------------Execute Test ----------------------
            string[] parameters = CreateParameters(null, null, null, null);
            DataTable tableDataTable = dbConnection.GetSchema("Tables", parameters);
            //---------------Test Result -----------------------
            Assert.IsNotNull(tableDataTable);
            Assert.AreEqual(1, tableDataTable.Rows.Count);
            //---------------Tear Down -------------------------
            dbConnection.Close();
        }
        [Test]
        public void Test_GetTablesDataTable_OneTable()
        {
            //---------------Set up test pack-------------------
            string tableName = CreateTable(_connection);
            DBSchemaExtractor dbSchema = GetDbSchemaExtractor(_connection);
            //---------------Assert Precondition----------------
            //---------------Execute Test ----------------------
            DataTable tableDataTable = dbSchema.GetTablesDataTable();

            //---------------Test Result -----------------------
            Assert.IsNotNull(tableDataTable);
            Assert.AreEqual(1, tableDataTable.Rows.Count);
            AssertContainsTable(tableDataTable, tableName);
        }

        [Test]
        public void Test_GetTablesDataTable_TwoTable()
        {
            //---------------Set up test pack-------------------

            string tableName = CreateTable(_connection);
            string tableName2 = CreateTable(_connection);

            DBSchemaExtractor dbSchema = GetDbSchemaExtractor(_connection);
            //-------------Test Pre-conditions --------------
            //-------------Execute test ---------------------
            DataTable tableDataTable = dbSchema.GetTablesDataTable();
            //-------------Test Result ----------------------
            Assert.IsNotNull(tableDataTable);
            Assert.AreEqual(2, tableDataTable.Rows.Count);
            AssertContainsTable(tableDataTable, tableName);
            AssertContainsTable(tableDataTable, tableName2);
        }


        #endregion

        #region Columns

        //[Test]
        //public void Test_GetSchema_OneColumn()
        //{
        //    //---------------Set up test pack-------------------
        //    string idcolumnName, idcolumnType;
        //    Table tableInfoWithID = CreateTableInfoWithID(out idcolumnName, out idcolumnType);
        //    CreateTableInDatabase(tableInfoWithID, _idbConnection);
        //    DbConnection dbConnection = (DbConnection) _idbConnection;
        //    string[] parameters = CreateParameters(_catalogName, _schemaName, tableInfoWithID.TableName, null);
        //    //---------------Assert Precondition----------------

        //    //---------------Execute Test ----------------------
        //    DataTable tableDataTable = dbConnection.GetSchema("Columns", parameters);

        //    //---------------Test Result -----------------------
        //    Assert.IsNotNull(tableDataTable);
        //    Assert.AreEqual(1, tableDataTable.Rows.Count);
        //    //---------------Tear Down -------------------------
        //    dbConnection.Close();
        //}



        [Test]
        public void Test_GetColumnsDataTable_IDColumns()
        {           
            //---------------Set up test pack-------------------
            const string idColumnName = "MyIDColumn";
            const string idColumnType = "CHAR";
            const string tableName = "MyTable";
            const int idColumnLength = 38;
            DBTableBuilder dbTableBuilder = new DBTableBuilder(new DBTable(tableName));
            dbTableBuilder.AddPrimaryKeyColumn(idColumnName, idColumnType, idColumnLength);
            DBTable dbTableWithIDAndCol = dbTableBuilder.Table;
            //Table dbTableWithIDAndCol = CreateTableInfoWithID(out idColumnName, out idColumnType);
            CreateTableInDatabase(dbTableWithIDAndCol, _connection);
            DBSchemaExtractor dbSchema = GetDbSchemaExtractor(_connection);
            //---------------Assert Precondition----------------
            //---------------Execute Test ----------------------
            DataTable columnDataTable = dbSchema.GetColumnsDataTable(tableName);
            //---------------Test Result -----------------------
            Assert.AreEqual(1, columnDataTable.Rows.Count);
            DataRow row = columnDataTable.Rows[0];
            Assert.AreEqual(dbSchema.CatalogName, DBUtilities.ConvertDBToString(row[DBSchemaTemplate.Naming.TableCatalog]));
            Assert.AreEqual(dbSchema.SchemaName, DBUtilities.ConvertDBToString(row[DBSchemaTemplate.Naming.TableSchema]));
            Assert.AreEqual(tableName.ToLower(), row[DBSchemaTemplate.Naming.TableName].ToString().ToLower());
            Assert.AreEqual(idColumnName.ToLower(), row[DBSchemaTemplate.Naming.ColumnName].ToString().ToLower());
            Assert.AreEqual(1, row[DBSchemaTemplate.Naming.OrdinalPosition]);
            Assert.AreEqual
                ("",
                 Convert.ToString
                     (DBUtilities.ConvertDBNullToNull(row[DBSchemaTemplate.Naming.ColumnDefault])),
                 "Column default should be null");
            Assert.IsFalse(DBUtilities.ConvertToBoolean(row[DBSchemaTemplate.Naming.ColumnIsNullable]));
            Assert.AreEqual(idColumnType.ToLower(), row[DBSchemaTemplate.Naming.DataType].ToString().ToLower());
            //Assert.AreEqual(38, DatabaseReverseEngineer.GetCharDataLength(row));
            Assert.AreEqual(38, row[DBSchemaTemplate.Naming.CharacterMaximumLength]);
            Assert.AreEqual(false.ToString(), row[DBSchemaTemplate.Naming.ColumnIsAutoIncrement]);
//            Assert.AreEqual(38, row[DBSchemaTemplate.Naming.CharacterMaximumLength]);
        }

        [Test]
        public virtual void TestGetColumn_ColumnDetailsCorrect_AutoIncrementing()
        {
            //---------------Set up test pack-------------------
            string columnName;// = "Column" + GetRandomInt();

            string idcolumnType;
            DBTable dbTableWithAutoIncID = CreateTableInfoWithAutoIncID(out columnName, out idcolumnType);
            string tableName = dbTableWithAutoIncID.TableName;//"Table" + GetRandomInt();
            
            CreateTableInDatabase(dbTableWithAutoIncID, _connection);

            //---------------Assert Precondition----------------
            //---------------Execute Test ----------------------
            DBSchemaExtractor dbSchema = GetDbSchemaExtractor(_connection);
            DataTable columnDataTable = dbSchema.GetColumnsDataTable(tableName);
            //---------------Test Result -----------------------
            Assert.AreEqual(1, columnDataTable.Rows.Count);
            DataRow row = columnDataTable.Rows[0];

            Assert.AreEqual(dbSchema.CatalogName, DBUtilities.ConvertDBToString(row[DBSchemaTemplate.Naming.TableCatalog]));
            Assert.AreEqual(dbSchema.SchemaName, DBUtilities.ConvertDBToString(row[DBSchemaTemplate.Naming.TableSchema]));
            Assert.AreEqual(tableName, row[DBSchemaTemplate.Naming.TableName]);
            Assert.AreEqual(columnName, row[DBSchemaTemplate.Naming.ColumnName]);
            Assert.AreEqual(1, row[DBSchemaTemplate.Naming.OrdinalPosition]);
            Assert.AreEqual
                ("",
                 Convert.ToString
                     (DBUtilities.ConvertDBNullToNull(row[DBSchemaTemplate.Naming.ColumnDefault])),
                 "Column default should be null");
            Assert.IsFalse(DBUtilities.ConvertToBoolean(row[DBSchemaTemplate.Naming.ColumnIsNullable]));
            Assert.AreEqual("int", row[DBSchemaTemplate.Naming.DataType].ToString().Substring(0,3).ToLower());
            Assert.AreEqual(null, DBUtilities.ConvertDBNullToNull(row[DBSchemaTemplate.Naming.CharacterMaximumLength]));
            Assert.AreEqual(true.ToString(), row[DBSchemaTemplate.Naming.ColumnIsAutoIncrement]);


            //Assert.AreEqual(columnName, row["COLUMN_NAME"]);
            //Assert.AreEqual("int", row["DATA_TYPE"]);
            //Assert.AreEqual(dataLength, DatabaseReverseEngineer.GetCharDataLength(row));
            //Assert.IsFalse(DatabaseReverseEngineer.ConvertToBoolean(row["IS_NULLABLE"]));
            //Assert.AreEqual
            //    ("", Convert.ToString(DatabaseReverseEngineer.ConvertDBNullToNull(row["COLUMN_DEFAULT"])),
            //     "Column default should be null");
            //Assert.AreEqual
            //    ("auto_increment", Convert.ToString(DatabaseReverseEngineer.ConvertDBNullToNull(row["EXTRA"])));
        }

        [Test]
        public void Test_GetColumnsDataTable_TwoColumns()
        {
            //---------------Set up test pack-------------------
            string idcolumnName;
            string idcolumnType;
            string columnDataType;
            string columnName;
            DBTable dbTable = CreateTableInfoWithIDAndCol(out idcolumnName, out idcolumnType, out columnName, out columnDataType);
            CreateTableInDatabase(dbTable, _connection);
            string tableName = dbTable.TableName;

            //---------------Assert Precondition----------------

            //---------------Execute Test ----------------------
            DBSchemaExtractor dbSchema = GetDbSchemaExtractor(_connection);
            DataTable columnDataTable = dbSchema.GetColumnsDataTable(tableName);
            //---------------Test Result -----------------------
            Assert.AreEqual(2, columnDataTable.Rows.Count);
            AssertContainsColumn(columnDataTable, columnName);
            AssertContainsColumn(columnDataTable, idcolumnName);
        }

        [Test]
        public void Test_GetColumnsDataTable_TwoTables()
        {
            // two tables correct columns on table. 
            //Ensure that you only get the columns back for the table that we are concerned with.
            //---------------Set up test pack-------------------
            string idcolumnName;
            string idcolumnType;
            string columnDataType;
            string columnName;
            DBTable dbTable = CreateTableInfoWithIDAndCol(out idcolumnName, out idcolumnType, out columnName, out columnDataType);
            CreateTableInDatabase(dbTable, _connection);
            string tableName = dbTable.TableName;

            string idcolumnName1;
            string idcolumnType1;
            CreateTableInDatabase(CreateTableInfoWithID(out idcolumnName1, out idcolumnType1), _connection);

            //---------------Assert Precondition----------------

            //---------------Execute Test ----------------------
            DBSchemaExtractor dbSchema = GetDbSchemaExtractor(_connection);
            DataTable columnDataTable = dbSchema.GetColumnsDataTable(tableName);
            //---------------Test Result -----------------------
            Assert.AreEqual(2, columnDataTable.Rows.Count);
            AssertContainsColumn(columnDataTable, columnName);
            AssertContainsColumn(columnDataTable, idcolumnName);
        }

        #endregion

        #region Keys

        //Key Data Table
//            dataTable.Columns.Add(Naming.TableCatalog);
//            dataTable.Columns.Add(Naming.TableSchema);
//            dataTable.Columns.Add(Naming.TableName);
//            dataTable.Columns.Add(Naming.KeyCatalog);
//            dataTable.Columns.Add(Naming.KeySchema);
//            dataTable.Columns.Add(Naming.KeyName);
        //Key Column Data Table

        
        /// <summary>
        ///            DataTable dataTable = new DataTable(DataTableNaming.KeyColumns);
        ///            dataTable.Columns.Add(Naming.TableCatalog);
        ///            dataTable.Columns.Add(Naming.TableSchema);
        ///            dataTable.Columns.Add(Naming.TableName);
        ///            dataTable.Columns.Add(Naming.KeyCatalog);
        ///            dataTable.Columns.Add(Naming.KeySchema);
        ///            dataTable.Columns.Add(Naming.KeyName);
        ///            dataTable.Columns.Add(Naming.ColumnName);
        ///            dataTable.Columns.Add(Naming.OrdinalPosition);
        /// </summary>
        [Test]
        public void Test_GetPrimaryKeyColumnsDataTable()
        {
            //---------------Set up test pack-------------------
            DBTable dbTableWithIDAndCol = GetTableInfoWithIDAndCol();
            CreateTableInDatabase(dbTableWithIDAndCol, _connection);
            string columnName = dbTableWithIDAndCol.PrimaryKeyDB.PrimaryKeyColumns[0].Column.ColumnName;
            string tableName = dbTableWithIDAndCol.TableName;
            DBSchemaExtractor dbSchema = GetDbSchemaExtractor(_connection);
            //--------------Assert PreConditions----------------
            Assert.AreEqual(2, dbTableWithIDAndCol.Columns.Count);
            Assert.AreEqual(0, dbTableWithIDAndCol.Keys.Count);
            Assert.IsNotNull(dbTableWithIDAndCol.PrimaryKeyDB);
            //---------------Execute Test ----------------------
            DataTable primaryKeyDataTable = dbSchema.GetPrimaryKeyColumnsDataTable(tableName);

            //---------------Test Result -----------------------
            DataRowCollection rows = primaryKeyDataTable.Rows;
            Assert.AreEqual(1, rows.Count);
            DataRow row = rows[0];
            AssertKeyRowDetailsCorrect(dbSchema, tableName, columnName, 1, row);
        } 

        [Test]
        public void Test_GetPrimaryKeyColumnsDataTable_NoPrimaryKey()
        {
            //---------------Set up test pack-------------------
            DBTable dbTableWithIDAndCol = GetTableInfoWithIDAndCol();
            dbTableWithIDAndCol.PrimaryKeyDB = null;
            CreateTableInDatabase(dbTableWithIDAndCol, _connection);
            string tableName = dbTableWithIDAndCol.TableName;

            DBSchemaExtractor dbSchema = GetDbSchemaExtractor(_connection);
            //--------------Assert PreConditions----------------
            Assert.AreEqual(2, dbTableWithIDAndCol.Columns.Count);
            Assert.AreEqual(0, dbTableWithIDAndCol.Keys.Count);
            Assert.IsNull(dbTableWithIDAndCol.PrimaryKeyDB);

            //---------------Execute Test ----------------------
            DataTable primaryKeyDataTable = dbSchema.GetPrimaryKeyColumnsDataTable(tableName);
            //---------------Test Result -----------------------
            DataRowCollection rows = primaryKeyDataTable.Rows;
            Assert.AreEqual(0, rows.Count);
        }

        [Test]
        public void Test_GetPrimaryKeyColumnsDataTable_HasAlternateKey()
        {
            //---------------Set up test pack-------------------
            DBTable dbTableWithIDAndCol = GetTableInfoWithIDAndAltKey();
            CreateTableInDatabase(dbTableWithIDAndCol, _connection);
            string columnName = dbTableWithIDAndCol.PrimaryKeyDB.PrimaryKeyColumns[0].Column.ColumnName;
            string tableName = dbTableWithIDAndCol.TableName;

            DBSchemaExtractor dbSchema = GetDbSchemaExtractor(_connection);
            //--------------Assert PreConditions----------------
            Assert.AreEqual(2, dbTableWithIDAndCol.Columns.Count);
            Assert.AreEqual(1, dbTableWithIDAndCol.Keys.Count);
            Assert.IsNotNull(dbTableWithIDAndCol.PrimaryKeyDB);

            //---------------Execute Test ----------------------
            DataTable primaryKeyDataTable = dbSchema.GetPrimaryKeyColumnsDataTable(tableName);

            //---------------Test Result -----------------------
            DataRowCollection rows = primaryKeyDataTable.Rows;
            Assert.AreEqual(1, rows.Count);
            DataRow row = rows[0];
            AssertKeyRowDetailsCorrect(dbSchema, tableName, columnName, 1, row);
        }

        

        /// <summary>
        ///            DataTable dataTable = new DataTable(DataTableNaming.KeyColumns);
        ///            dataTable.Columns.Add(Naming.TableCatalog);
        ///            dataTable.Columns.Add(Naming.TableSchema);
        ///            dataTable.Columns.Add(Naming.TableName);
        ///            dataTable.Columns.Add(Naming.KeyCatalog);
        ///            dataTable.Columns.Add(Naming.KeySchema);
        ///            dataTable.Columns.Add(Naming.KeyName);
        ///            dataTable.Columns.Add(Naming.ColumnName);
        ///            dataTable.Columns.Add(Naming.OrdinalPosition);
        /// </summary>
        [Test]
        public void Test_GetPrimaryKeyColumnsDataTable_CompositePrimaryKey()
        {
            //---------------Set up test pack-------------------
//            _dataTableUtils.CreateDatabaseTableWithCompositePrimaryKey(tableName);
            DBTable dbTableWithCompositeKey = CreateTableInfoWithCompositeID();
            CreateTableInDatabase(dbTableWithCompositeKey, _connection);
            string columnName = dbTableWithCompositeKey.PrimaryKeyDB.PrimaryKeyColumns[0].Column.ColumnName;
            string tableName = dbTableWithCompositeKey.TableName;

            DBSchemaExtractor dbSchema = GetDbSchemaExtractor(_connection);
            //--------------Assert PreConditions----------------
            Assert.AreEqual(2, dbTableWithCompositeKey.Columns.Count);
            Assert.AreEqual(0, dbTableWithCompositeKey.Keys.Count);
            Assert.IsNotNull(dbTableWithCompositeKey.PrimaryKeyDB);
            //---------------Execute Test ----------------------
            DataTable primaryKeyDataTable = dbSchema.GetPrimaryKeyColumnsDataTable(tableName);
            //---------------Test Result -----------------------
            DataRowCollection rows = primaryKeyDataTable.Rows;
            Assert.AreEqual(2, rows.Count);

            //First Column
            DataRow row = rows[0];
            AssertKeyRowDetailsCorrect(dbSchema, tableName, columnName, 1, row);

            //SecondColumn
            row = rows[1];
            columnName = dbTableWithCompositeKey.PrimaryKeyDB.PrimaryKeyColumns[1].Column.ColumnName;
            AssertKeyRowDetailsCorrect(dbSchema, tableName, columnName, 2, row);
        }

        [Test]
        public void Test_GetAlternateKeysDataTable_NoAlternateKey()
        {
            //---------------Set up test pack-------------------
            DBTable dbTableWithIDAndCol = GetTableInfoWithIDAndCol();
            CreateTableInDatabase(dbTableWithIDAndCol, _connection);
            string tableName = dbTableWithIDAndCol.TableName;

            DBSchemaExtractor dbSchema = GetDbSchemaExtractor(_connection);
            //--------------Assert PreConditions----------------
            //---------------Execute Test ----------------------
            DataTable alternateKeyDataTable = dbSchema.GetUniqueKeyColumnsDataTable(tableName);

            //---------------Test Result -----------------------
            Assert.AreEqual(0, alternateKeyDataTable.Rows.Count);
        }

        [Test]
        public void Test_GetAlternateKeysDataTable_HasAlternateKey()
        {
            //---------------Set up test pack-------------------
            DBTable dbTableWithAltKey = GetTableInfoWithIDAndAltKey();
            CreateTableInDatabase(dbTableWithAltKey, _connection);
            string tableName = dbTableWithAltKey.TableName;
            DBKey dbKey = dbTableWithAltKey.Keys[0];

            DBSchemaExtractor dbSchema = GetDbSchemaExtractor(_connection);
            //--------------Assert PreConditions----------------
            //---------------Execute Test ----------------------
            DataTable alternateKeyDataTable = dbSchema.GetUniqueKeyColumnsDataTable(tableName);

            //---------------Test Result -----------------------
            Assert.AreEqual(1, alternateKeyDataTable.Rows.Count);
            AssertNotContainsIndex("PRIMARY", alternateKeyDataTable);
            AssertContainsIndex(dbKey.KeyName, alternateKeyDataTable);
        }

        [Test]
        public void Test_GetAlternateKeysDataTable_HasTwoAlternateKeys()
        {
            //---------------Set up test pack-------------------
            DBTable dbTableWithAltKey = GetTableInfoWithIDAndTwoAltKeys();
            CreateTableInDatabase(dbTableWithAltKey, _connection);
            string tableName = dbTableWithAltKey.TableName;
            DBKey dbKey = dbTableWithAltKey.Keys[0];

            DBSchemaExtractor dbSchema = GetDbSchemaExtractor(_connection);
            //--------------Assert PreConditions----------------
            Assert.AreEqual(2, dbTableWithAltKey.Keys.Count);
            //---------------Execute Test ----------------------
            DataTable alternateKeyDataTable = dbSchema.GetUniqueKeyColumnsDataTable(tableName);

            //---------------Test Result -----------------------
            Assert.AreEqual(2, alternateKeyDataTable.Rows.Count);
            AssertNotContainsIndex("PRIMARY", alternateKeyDataTable);
            AssertContainsIndex(dbKey.KeyName, alternateKeyDataTable);
        }

        [Test]
        public void Test_GetAlternateKeyColumnsDataTable_NoAlternateKey()
        {
            //---------------Set up test pack-------------------
            DBTable dbTableWithIDAndCol = GetTableInfoWithIDAndCol();
            CreateTableInDatabase(dbTableWithIDAndCol, _connection);
            string tableName = dbTableWithIDAndCol.TableName;

            DBSchemaExtractor dbSchema = GetDbSchemaExtractor(_connection);
            //--------------Assert PreConditions----------------
            //---------------Execute Test ----------------------
            DataTable alternateKeyDataTable = dbSchema.GetUniqueKeyColumnsDataTable(tableName);

            //---------------Test Result -----------------------
            Assert.AreEqual(0, alternateKeyDataTable.Rows.Count);
        }

        [Test]
        public void Test_GetAlternateKeyColumnsDataTable_TableHasAlternateKey()
        {
            //---------------Set up test pack-------------------
            DBTable dbTableWithAltKey = GetTableInfoWithIDAndAltKey();
            CreateTableInDatabase(dbTableWithAltKey, _connection);
            string tableName = dbTableWithAltKey.TableName;
            DBKey dbKey = dbTableWithAltKey.Keys[0];
            string columnName = dbKey.KeyColumns[0].Column.ColumnName;
            //--------------Assert PreConditions----------------
            //---------------Execute Test ----------------------
            DBSchemaExtractor dbSchema = GetDbSchemaExtractor(_connection);
            DataTable alternateKeyDataTable = dbSchema.GetUniqueKeyColumnsDataTable(tableName);

            //---------------Test Result -----------------------
            Assert.AreEqual(1, alternateKeyDataTable.Rows.Count);
            AssertNotContainsIndex("PRIMARY", alternateKeyDataTable);
//            AssertNotContainsIndexField(dbTableWithAltKey.PrimaryKeyDB.PrimaryKeyColumns[0].Column.ColumnName, alternateKeyDataTable);

            AssertContainsIndex(dbKey.KeyName, alternateKeyDataTable);
            AssertContainsIndexField(columnName, alternateKeyDataTable);
        }

        [Test]
        public void Test_GetAlternateKeyColumnsDataTable_HasTwoAlternateKeys()
        {
            //---------------Set up test pack-------------------
            DBTable dbTableWithAltKey = GetTableInfoWithIDAndTwoAltKeys();
            CreateTableInDatabase(dbTableWithAltKey, _connection);
            string tableName = dbTableWithAltKey.TableName;


            DBSchemaExtractor dbSchema = GetDbSchemaExtractor(_connection);
            //--------------Assert PreConditions----------------
            Assert.AreEqual(2, dbTableWithAltKey.Keys.Count);
            //---------------Execute Test ----------------------
            DataTable alternateKeyDataTable = dbSchema.GetUniqueKeyColumnsDataTable(tableName);

            //---------------Test Result -----------------------
            Assert.AreEqual(2, alternateKeyDataTable.Rows.Count);
            AssertNotContainsIndex("PRIMARY", alternateKeyDataTable);
            AssertContainsIndex(dbTableWithAltKey.Keys[0].KeyName, alternateKeyDataTable);



            //First Column
            DBKey firstDBKey = dbTableWithAltKey.Keys[0];
            DBKey secondDBKey = dbTableWithAltKey.Keys[1];
            if (secondDBKey.KeyName.CompareTo(firstDBKey.KeyName) == -1)
            {
                DBKey dbKey = firstDBKey;
                firstDBKey = secondDBKey;
                secondDBKey = dbKey;
            }

            AssertContainsIndex(firstDBKey.KeyName, alternateKeyDataTable);
            DataRow row = alternateKeyDataTable.Rows[0];
            AssertSingleRowKeyDetailsCorrect(dbSchema, tableName, firstDBKey, row);

            //Second Key
            AssertContainsIndex(secondDBKey.KeyName, alternateKeyDataTable);
            row = alternateKeyDataTable.Rows[1];
            AssertSingleRowKeyDetailsCorrect(dbSchema, tableName, secondDBKey, row);
        }

        private static void AssertSingleRowKeyDetailsCorrect(IDBSchemaExtractor dbSchema, string tableName, DBKey dbKey, DataRow row)
        {
            string columnName = dbKey.KeyColumns[0].Column.ColumnName;
            Assert.AreEqual(dbKey.KeyName, row[DBSchemaTemplate.Naming.KeyName]);
            Assert.AreEqual(tableName, row[DBSchemaTemplate.Naming.TableName]);
            Assert.AreEqual(dbSchema.CatalogName, DBUtilities.ConvertDBToString(row[DBSchemaTemplate.Naming.KeyCatalog]));
            Assert.AreEqual(dbSchema.SchemaName, DBUtilities.ConvertDBToString(row[DBSchemaTemplate.Naming.KeySchema]));
            Assert.AreEqual(dbSchema.CatalogName, DBUtilities.ConvertDBToString(row[DBSchemaTemplate.Naming.TableCatalog]));
            Assert.AreEqual(dbSchema.SchemaName, DBUtilities.ConvertDBToString(row[DBSchemaTemplate.Naming.TableSchema]));
            Assert.AreEqual(columnName, row[DBSchemaTemplate.Naming.ColumnName]);
            Assert.AreEqual(1, row[DBSchemaTemplate.Naming.OrdinalPosition]);
        }

        [Test]
        public void Test_GetKeysDataTable_TableCompositeAlternateKeys()
        {
            //---------------Set up test pack-------------------
            DBTable dbTableWithAltKey = GetTableInfoWithIDAndCompositeAltKeys();
            CreateTableInDatabase(dbTableWithAltKey, _connection);
            string tableName = dbTableWithAltKey.TableName;
            DBSchemaExtractor dbSchema = GetDbSchemaExtractor(_connection);
            DBKey dbKey = dbTableWithAltKey.Keys[0];

            //--------------Assert PreConditions----------------
            Assert.AreEqual(1, dbTableWithAltKey.Keys.Count);
            Assert.AreEqual(2, dbKey.KeyColumns.Count);
            //---------------Execute Test ----------------------
            DataTable alternateKeyDataTable = dbSchema.GetUniqueKeyColumnsDataTable(tableName);

            //---------------Test Result -----------------------
            Assert.AreEqual(2, alternateKeyDataTable.Rows.Count);
            AssertNotContainsIndex("PRIMARY", alternateKeyDataTable);

            //First Column
            AssertContainsIndex(dbKey.KeyName, alternateKeyDataTable);
            string columnName = dbKey.KeyColumns[0].Column.ColumnName;
            AssertContainsIndex(dbKey.KeyName, alternateKeyDataTable);
            DataRow row = alternateKeyDataTable.Rows[0];
            Assert.AreEqual(dbKey.KeyName, row[DBSchemaTemplate.Naming.KeyName]);
            AssertKeyRowDetailsCorrect(dbSchema, tableName, columnName, 1, row);

            //SecondColumn
            row = alternateKeyDataTable.Rows[1];
            columnName = dbKey.KeyColumns[1].Column.ColumnName;
            Assert.AreEqual(dbKey.KeyName, row[DBSchemaTemplate.Naming.KeyName]);
            AssertKeyRowDetailsCorrect(dbSchema, tableName, columnName, 2, row);
        }

        #endregion

        #region ForeignKey


        [Test]
        public void Test_GetForeignKeyDataTable()
        {
            //---------------Set up test pack-------------------
            DBTable referencedDBTable;

            DBTable dbTableWithAltKey = CreateTableInfoWithForeignKey(out referencedDBTable);
            CreateTableInDatabase(referencedDBTable, _connection);
            CreateTableInDatabase(dbTableWithAltKey, _connection);
            //string tableName = dbTableWithAltKey.TableName;

            DBForeignKey key = dbTableWithAltKey.ForeignKeys[0];

            DBSchemaExtractor dbSchema = GetDbSchemaExtractor(_connection);

            //---------------Assert Precondition----------------
            Assert.AreEqual(1, dbTableWithAltKey.ForeignKeys.Count);
            Assert.AreEqual(1, key.ForeignKeyColumns.Count);
            Assert.AreEqual(1, referencedDBTable.RelatedForeignKeys.Count);
            //---------------Execute Test ----------------------
            DataTable foreignKeySchemaInformation = dbSchema.GetForeignKeyColumnsDataTable(dbTableWithAltKey.TableName, null);
            //---------------Test Result -----------------------
            Assert.IsNotNull(foreignKeySchemaInformation);
            Assert.AreEqual(1, foreignKeySchemaInformation.Rows.Count);

            DataRow row = foreignKeySchemaInformation.Rows[0];
            string columnName = key.ForeignKeyColumns[0].ParentColumn.ColumnName;
            Assert.AreEqual(key.ForeignKeyName, row[DBSchemaTemplate.Naming.KeyName]);
            AssertKeyRowDetailsCorrect(dbSchema, dbTableWithAltKey.TableName, columnName, 1, row);
        }

        [Test]
        public void Test_GetForeignKeyDataTable_CompositeForeignKey()
        {
            //---------------Set up test pack-------------------
            DBTable referencedDBTable;
            DBTable dbTableWithForeignKey = CreateTableInfoWithCompositeForeignKey(out referencedDBTable);
            CreateTableInDatabase(referencedDBTable, _connection);
            CreateTableInDatabase(dbTableWithForeignKey, _connection);
            string tableName = dbTableWithForeignKey.TableName;
            DBForeignKey key = dbTableWithForeignKey.ForeignKeys[0];
            DBSchemaExtractor dbSchema = GetDbSchemaExtractor(_connection);
            //---------------Assert Precondition----------------
            Assert.AreEqual(1, dbTableWithForeignKey.ForeignKeys.Count);
            Assert.AreEqual(2, key.ForeignKeyColumns.Count);
            Assert.AreEqual(1, referencedDBTable.RelatedForeignKeys.Count);
            Assert.AreEqual(2, referencedDBTable.PrimaryKeyDB.PrimaryKeyColumns.Count);
            //---------------Execute Test ----------------------
            DataTable foreignKeySchemaInformation = dbSchema.GetForeignKeyColumnsDataTable(tableName, null);
            //---------------Test Result -----------------------
            Assert.IsNotNull(foreignKeySchemaInformation);
            Assert.AreEqual(2, foreignKeySchemaInformation.Rows.Count);

            //Column1
            int rowIndex = 0;
            AssertForeignKeyRowCorrect(dbSchema, foreignKeySchemaInformation, rowIndex, tableName, key);

            //Column2
            rowIndex = 1;
            AssertForeignKeyRowCorrect(dbSchema, foreignKeySchemaInformation, rowIndex, tableName, key);
        }

        private static void AssertForeignKeyRowCorrect(IDBSchemaExtractor schemaExtractor, DataTable foreignKeySchemaInformation, int rowIndex, string tableName, DBForeignKey key)
        {
            AssertForeignKeyRowCorrect(schemaExtractor, foreignKeySchemaInformation, rowIndex, tableName, key, 0);
        }

        private static void AssertForeignKeyRowCorrect(IDBSchemaExtractor schemaExtractor, DataTable foreignKeySchemaInformation, int rowIndex, string tableName, DBForeignKey key, int foreignKeyIndex)
        {
            DataRow row = foreignKeySchemaInformation.Rows[rowIndex];
            int expectedOrdinalPos = rowIndex + 1;
            string columnName = key.ForeignKeyColumns[rowIndex].ParentColumn.ColumnName;
            AssertForeignKeyRowCorrect(schemaExtractor, tableName, key.ForeignKeyName, row, columnName, expectedOrdinalPos);
        }

        private static void AssertForeignKeyRowCorrect(IDBSchemaExtractor schemaExtractor, string tableName, string fkName, DataRow row, string columnName, int expectedOrdinalPos)
        {
            Assert.AreEqual(fkName, row[DBSchemaTemplate.Naming.KeyName]);
            AssertKeyRowDetailsCorrect(schemaExtractor, tableName, columnName, expectedOrdinalPos, row);
        }

//        [Test]
//        public void Test_CreateForeignKeySchema()
//        {
//            //---------------Set up test pack-------------------
//            const string foreignKeyTableName = "fktablename";
//            const string referencedtablename = "referencedTableName";
//            _dataTableUtils.CreateDatabaseTablesWithForeignKeyTwoColumns
//                (foreignKeyTableName, referencedtablename, "fkcolumnname");
//            //---------------Assert Precondition----------------
//
//            //---------------Execute Test ----------------------
//            DBSchemaExtractor dbSchema = GetDbSchemaExtractor(_databaseConnection.GetConnection());
//            DataTable foreignKeySchemaInformation = dbSchema.GetForeignKeyColumnsDataTable
//                (_catalogName, _schemaName, foreignKeyTableName, null);
//            //---------------Test Result -----------------------
//            Assert.IsNotNull(foreignKeySchemaInformation);
//            Assert.AreEqual(2, foreignKeySchemaInformation.Rows.Count);
//            AssertContainsRow(foreignKeySchemaInformation, "TABLE_NAME", foreignKeyTableName);
//            AssertContainsRow(foreignKeySchemaInformation, "REFERENCED_TABLE_NAME", referencedtablename);
//            AssertContainsRow(foreignKeySchemaInformation, "REFERENCED_COLUMN_NAME", referencedtablename + "ID1");
//            AssertContainsRow(foreignKeySchemaInformation, "REFERENCED_COLUMN_NAME", referencedtablename + "ID2");
//            AssertContainsRow(foreignKeySchemaInformation, "COLUMN_NAME", "fkcolumnname" + "FK1");
//            AssertContainsRow(foreignKeySchemaInformation, "COLUMN_NAME", "fkcolumnname" + "FK2");
//        }

        [Test]
        public void Test_CreateForeignKeySchema_TwoForeignKeys()
        {
            //---------------Set up test pack-------------------
//            const string foreignKeyTableName = "fktablename";
//            const string referencedtablename = "referencedtablename";
//            const string fkcolumnname = "fkcolumnname";
//            _dataTableUtils.CreateDatabaseTablesWithTwoForeignKeysTwoColumns(foreignKeyTableName, referencedtablename, fkcolumnname);
            DBTable referencedDBTable;
            DBTable dbTableWith2ForeignKeys = CreateTableInfoWithForeignKey(out referencedDBTable);
            CreateTableInDatabase(referencedDBTable, _connection);
            referencedDBTable = AddCompositeForeignKeyToTable(dbTableWith2ForeignKeys);
            CreateTableInDatabase(referencedDBTable, _connection);
            CreateTableInDatabase(dbTableWith2ForeignKeys, _connection);
            DBSchemaExtractor dbSchema = GetDbSchemaExtractor(_connection);

            //---------------Assert Precondition----------------
            Assert.AreEqual(2, dbTableWith2ForeignKeys.ForeignKeys.Count);
            Assert.AreEqual(1, dbTableWith2ForeignKeys.ForeignKeys[0].ForeignKeyColumns.Count);
            Assert.AreEqual(2, dbTableWith2ForeignKeys.ForeignKeys[1].ForeignKeyColumns.Count);
            //---------------Execute Test ----------------------
            DataTable foreignKeySchemaInfo = dbSchema.GetForeignKeyColumnsDataTable(dbTableWith2ForeignKeys.TableName, null);
            //---------------Test Result -----------------------
            Assert.IsNotNull(foreignKeySchemaInfo);
            Assert.AreEqual(3, foreignKeySchemaInfo.Rows.Count);
            AssertContainsRow(foreignKeySchemaInfo, DBSchemaTemplate.Naming.KeyName, dbTableWith2ForeignKeys.ForeignKeys[0].ForeignKeyName);
            AssertContainsRow(foreignKeySchemaInfo, DBSchemaTemplate.Naming.KeyName, dbTableWith2ForeignKeys.ForeignKeys[1].ForeignKeyName);
//            DataRow row = GetRow(foreignKeySchemaInfo, "CONSTRAINT_NAME", foreignKeyTableName + "_ibfk_2");
//            Assert.AreEqual(foreignKeyTableName, row["TABLE_NAME"]);
//            Assert.AreEqual(foreignKeyTableName + "_ibfk_2", row["CONSTRAINT_NAME"]);
//            Assert.AreEqual(fkcolumnname + "FK1", row["COLUMN_NAME"]);
//            Assert.AreEqual(referencedtablename, row["REFERENCED_TABLE_NAME"]);
//            Assert.AreEqual(referencedtablename + "ID1", row["REFERENCED_COLUMN_NAME"]);
//
//            Assert.IsNotNull(foreignKeySchemaInfo);
//            Assert.AreEqual(1, foreignKeySchemaInfo.Rows.Count);
//
//            DataRow row = foreignKeySchemaInfo.Rows[0];
//            string columnName = key.ForeignKeyColumns[0].ParentColumn.ColumnName;
//            Assert.AreEqual(tableName + "_ibfk_1", row[DBSchemaTemplate.Naming.KeyName]);
//            Assert.AreEqual(tableName, row[DBSchemaTemplate.Naming.TableName]);
//            Assert.AreEqual(dbSchema.CatalogName, row[DBSchemaTemplate.Naming.KeyCatalog]);
//            Assert.AreEqual(dbSchema.SchemaName, row[DBSchemaTemplate.Naming.KeySchema]);
//            Assert.AreEqual(dbSchema.CatalogName, DBUtilities.ConvertDBToString(row[DBSchemaTemplate.Naming.TableCatalog]));
//            Assert.AreEqual(dbSchema.SchemaName, DBUtilities.ConvertDBToString(row[DBSchemaTemplate.Naming.TableSchema]));
//            Assert.AreEqual(columnName, row[DBSchemaTemplate.Naming.ColumnName]);
//            Assert.AreEqual(1, row[DBSchemaTemplate.Naming.OrdinalPosition]);

            //Column1
            
            string tableName = dbTableWith2ForeignKeys.TableName;
            DBForeignKey key = dbTableWith2ForeignKeys.ForeignKeys[0];
            DataRow row = TestUtilsDataTable.GetRow(foreignKeySchemaInfo, DBSchemaTemplate.Naming.ColumnName, key.ForeignKeyColumns[0].ParentColumn.ColumnName);
            AssertForeignKeyRowCorrect
                (dbSchema, tableName, key.ForeignKeyName, row, key.ForeignKeyColumns[0].ParentColumn.ColumnName, 1);



            //Column2
            const int foreignKeyIndex = 1;
            key = dbTableWith2ForeignKeys.ForeignKeys[foreignKeyIndex];
            row = TestUtilsDataTable.GetRow(foreignKeySchemaInfo, DBSchemaTemplate.Naming.ColumnName, key.ForeignKeyColumns[0].ParentColumn.ColumnName);
            AssertForeignKeyRowCorrect
                (dbSchema, tableName, key.ForeignKeyName, row, key.ForeignKeyColumns[0].ParentColumn.ColumnName, 1);
//            AssertForeignKeyRowCorrect(foreignKeySchemaInfo, rowIndex, tableName, key, foreignKeyIndex);

//            //Column3
            key = dbTableWith2ForeignKeys.ForeignKeys[foreignKeyIndex];
            row = TestUtilsDataTable.GetRow(foreignKeySchemaInfo, DBSchemaTemplate.Naming.ColumnName, key.ForeignKeyColumns[1].ParentColumn.ColumnName);
            AssertForeignKeyRowCorrect(dbSchema, tableName, key.ForeignKeyName, row, key.ForeignKeyColumns[1].ParentColumn.ColumnName, 2);
        }

        #endregion

        #region Utils

        private static void AssertKeyRowDetailsCorrect(IDBSchemaExtractor dbSchema, string tableName, string columnName, int ordinalPosition, DataRow row)
        {
            Assert.AreNotEqual(0, Convert.ToString(row[DBSchemaTemplate.Naming.KeyName]).Length);
            Assert.AreEqual(tableName, row[DBSchemaTemplate.Naming.TableName]);
            Assert.AreEqual(dbSchema.CatalogName, DBUtilities.ConvertDBToString(row[DBSchemaTemplate.Naming.KeyCatalog]));
            Assert.AreEqual(dbSchema.SchemaName, DBUtilities.ConvertDBToString(row[DBSchemaTemplate.Naming.KeySchema]));
            Assert.AreEqual(dbSchema.CatalogName, DBUtilities.ConvertDBToString(row[DBSchemaTemplate.Naming.TableCatalog]));
            Assert.AreEqual(dbSchema.SchemaName, DBUtilities.ConvertDBToString(row[DBSchemaTemplate.Naming.TableSchema]));
            Assert.AreEqual(columnName, row[DBSchemaTemplate.Naming.ColumnName]);
            Assert.AreEqual(ordinalPosition, row[DBSchemaTemplate.Naming.OrdinalPosition]);
        }

        private static void AssertContainsTable(DataTable tableDataTable, string tableName)
        {
            AssertContainsRow(tableDataTable, "TABLE_NAME", tableName);
        }

        private static void AssertContainsColumn(DataTable columns, string columnName)
        {
            AssertContainsRow(columns, "COLUMN_NAME", columnName);
        }


        private static void AssertContainsIndex(string indexName, DataTable indexes)
        {
            AssertContainsRow(indexes, DBSchemaTemplate.Naming.KeyName, indexName);
        }

        private static void AssertNotContainsIndex(string indexName, DataTable indexes)
        {
            AssertNotContainsRow(indexes, DBSchemaTemplate.Naming.KeyName, indexName);
        }


        private static void AssertContainsRow(DataTable dataTable, string fieldName, string fieldValue)
        {
            Assert.IsTrue
                (TestUtilsDataTable.ContainsRow(dataTable, fieldName, fieldValue),
                 "DataTable should contain row with column " + fieldName + " = " + fieldValue);
        }

        private static void AssertNotContainsRow(DataTable dataTable, string fieldName, string fieldValue)
        {
            Assert.IsFalse
                (TestUtilsDataTable.ContainsRow(dataTable, fieldName, fieldValue),
                 "DataTable should contain row with column " + fieldName + " = " + fieldValue);
        }

        private static void AssertContainsIndexField(string indexFieldName, DataTable indexFields)
        {
            AssertContainsRow(indexFields, DBSchemaTemplate.Naming.ColumnName, indexFieldName);
        }

        #endregion

        #region InvestigateSchema

        //        [Test, 
        [Ignore("This should only be explicitly run as part of investigating the shema.")]
        [Test]
        public void InvestigateSchema()
        {
            //---------------Set up test pack-------------------
            DbConnection connection = (DbConnection) _connection;

            if (connection.State == ConnectionState.Closed) connection.Open();
            DataTable schemaCollections = connection.GetSchema("MetaDataCollections");
            //OutputDataTableColumns(schemaCollections);
            foreach (DataRow schemaCollectionDataRow in schemaCollections.Rows)
            {
                string schemaCollectionName = schemaCollectionDataRow[0].ToString();
                try
                {
                    DataTable dataTable = connection.GetSchema(schemaCollectionName);
                    if (schemaCollectionName == "DataTypes")
                    {
                        OutputDataTable(dataTable);
                    }
                    else
                    {
                        OutputDataTableColumns(dataTable);
                    }
                }
                catch (Exception)
                {
                    Console.WriteLine(string.Format("***** Error Retrieving {0}", schemaCollectionName));
                }
            }

            //-------------Assert Preconditions -------------


            //---------------Execute Test ----------------------

            //---------------Test Result -----------------------
        }

        private static void OutputDataTableColumns(DataTable dataTable)
        {
            Console.WriteLine("-------------- {0} --------------", dataTable);
            List<string> columnNames = new List<string>();
            foreach (DataColumn dataColumn in dataTable.Columns)
            {
                columnNames.Add(dataColumn.ColumnName);
            }
            Console.WriteLine(String.Join(", ", columnNames.ToArray()));
            //StringWriter stringWriter = new StringWriter();
            //dataTable.WriteXml(stringWriter);
            //Console.WriteLine(stringWriter.ToString());
        }

        private static void OutputDataTable(DataTable dataTable)
        {
            OutputDataTableColumns(dataTable);

            foreach (DataRow dataRow in dataTable.Rows)
            {
                List<string> values = new List<string>();
                foreach (object value in dataRow.ItemArray)
                {
                    values.Add(Convert.ToString(value));
                }
                Console.WriteLine(String.Join(";", values.ToArray()));
            }

            //StringWriter stringWriter = new StringWriter();
            //dataTable.WriteXml(stringWriter);
            //Console.WriteLine(stringWriter.ToString());
        }

        #endregion

        #region OtherDatabaseVendors


        //        [TestFixture]
        //        public class TestDBReverseEng_GetSchema_MySQL : TestDBReverseEng_GetSchema
        //        {
        //            [TestFixtureSetUp]
        //            public override void TestFixtureSetup()
        //            {
        //                //Code that is executed before any test is run in this class. If multiple tests
        //                // are executed then it will still only be called once.
        //                ClassDef.ClassDefs.Clear();
        //                ClassDef.LoadClassDefs(new XmlClassDefsLoader(new StreamReader("Classdefs.xml").ReadToEnd(), new DtdLoader()));
        //                DatabaseConfig databaseConfig = GetDatabaseConfig("Mysql", "localhost", _testDatabaseName, "root", "root", "3306");
        //                DatabaseConfig newConfig = CloneDatabaseConfig(databaseConfig, null, null, _testDatabaseName, null, null, null);
        //                _databaseConnection = newConfig.GetDatabaseConnection();
        //
        //                _dataTableUtils = new DataTableUtilsMySQL(_databaseConnection);
        //            }
        //
        //            [SetUp]
        //            public override void SetupTest()
        //            {
        //                _databaseConnection.GetConnection().Close();
        //                DatabaseConfig databaseConfig = GetDatabaseConfig("Mysql","localhost",_testDatabaseName,"root","root","3306");
        //                DatabaseConfig newConfig = CloneDatabaseConfig(databaseConfig, null, null, _testDatabaseName, null, null, null);
        //                _databaseConnection = newConfig.GetDatabaseConnection();
        //                if (DatabaseConnection.CurrentConnection == null)
        //                {
        //                    //DatabaseConnection.CurrentConnection = databaseConfig.GetDatabaseConnection();
        //                    //BORegistry.DataAccessor = new DataAccessorDB();
        //                    BORegistry.DataAccessor = new DataAccessorInMemory();
        //                }
        //                //  base.SetupTest();
        //            }
        //            private DatabaseConfig GetDatabaseConfig(string databaseType, string serverName, string dataBaseName, string userName, string passWord, string port)
        //            {
        //
        //                DatabaseConfig databaseConfig = new DatabaseConfig(databaseType, serverName, dataBaseName, userName, passWord, port);
        //                IDatabaseConnection databaseConnection = databaseConfig.GetDatabaseConnection();
        //                string sqlStatement = string.Format(@"
        //                DROP DATABASE {0};
        //                CREATE DATABASE {0};
        //                USE {0};
        //                ", _testDatabaseName);
        //                databaseConnection.ExecuteRawSql(sqlStatement);
        //                return databaseConfig;
        //            }
        //        }

        //[TestFixture]
        //public class TestDBReverseEng_GetSchema_Oracle : TestDBReverseEng_GetSchema
        //{
        //    [TestFixtureSetUp]
        //    public override void TestFixtureSetup()
        //    {
        //        //Code that is executed before any test is run in this class. If multiple tests
        //        // are executed then it will still only be called once.
        //        ClassDef.ClassDefs.Clear();
        //        ClassDef.LoadClassDefs(new XmlClassDefsLoader(new StreamReader("Classdefs.xml").ReadToEnd(), new DtdLoader()));
        //        DatabaseConfig databaseConfig = GetDatabaseConfig("Oracle", "localhost", "(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = Core1)(PORT = 1521)))(CONNECT_DATA = (SERVICE_NAME = XE)))", "mark", "mark", null);
        //        _databaseConnection = databaseConfig.GetDatabaseConnection();
        //    }

        //    [SetUp]
        //    public override void SetupTest()
        //    {

        //            BORegistry.DataAccessor = new DataAccessorInMemory();

        //    }
        //    private DatabaseConfig GetDatabaseConfig(string databaseType, string serverName, string dataBaseName, string userName, string passWord, string port)
        //    {
        //        DatabaseConfig databaseConfig = new DatabaseConfig(databaseType, serverName, dataBaseName, userName, passWord, port);
        //        return databaseConfig;
        //    }
        //}

        #endregion
    }
}